﻿using System;
using System.Windows;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
using System.Collections;
using System.Globalization;

namespace HREDU.Hoang_Tuan.Classes
{
    public class CLUONG
    {
        private int _id_luong;
        private int _id_nhanvien;
		private int _id_nbl;
		private System.DateTime _namxepluong;
		private int _phucaptnvk;
		private float _hesoclbl;
        private float _hesoclbl2;
		private int _id_lydonl;
        private string _ghichu;

        #region "Properties"

        /// <summary>
        /// Gets or sets the <c>ID_NHANVIEN</c> column value.
        /// </summary>
        /// <value>The <c>ID_NHANVIEN</c> column value.</value>
        public int ID_LUONG
        {
            get { return _id_luong; }
            set { _id_luong = value; }
        }

        /// <summary>
		/// Gets or sets the <c>ID_NHANVIEN</c> column value.
		/// </summary>
		/// <value>The <c>ID_NHANVIEN</c> column value.</value>
		public int ID_NHANVIEN
		{
			get { return _id_nhanvien; }
			set { _id_nhanvien = value; }
		}

		/// <summary>
		/// Gets or sets the <c>ID_NBL</c> column value.
		/// </summary>
		/// <value>The <c>ID_NBL</c> column value.</value>
		public int ID_NBL
		{
			get { return _id_nbl; }
			set { _id_nbl = value; }
		}

		/// <summary>
		/// Gets or sets the <c>NAMXEPLUONG</c> column value.
		/// </summary>
		/// <value>The <c>NAMXEPLUONG</c> column value.</value>
		public System.DateTime NAMXEPLUONG
		{
			get { return _namxepluong; }
			set { _namxepluong = value; }
		}

		/// <summary>
		/// Gets or sets the <c>PHUCAPTNVK</c> column value.
		/// This column is nullable.
		/// </summary>
		/// <value>The <c>PHUCAPTNVK</c> column value.</value>
		public int PHUCAPTNVK
		{
			get
			{
				return _phucaptnvk;
			}
			set
			{
    			_phucaptnvk = value;
			}
		}

		/// <summary>
		/// Gets or sets the <c>HESOCLBL</c> column value.
		/// This column is nullable.
		/// </summary>
		/// <value>The <c>HESOCLBL</c> column value.</value>
		public float HESOCLBL
		{
			get
			{
				return _hesoclbl;
			}
			set
			{
				_hesoclbl = value;
			}
		}

        /// <summary>
        /// Gets or sets the <c>HESOCLBL2</c> column value.
        /// This column is nullable.
        /// </summary>
        /// <value>The <c>HESOCLBL</c> column value.</value>
        public float HESOCLBL2
        {
            get
            {
                return _hesoclbl2;
            }
            set
            {
                _hesoclbl2 = value;
            }
        }

		/// <summary>
		/// Gets or sets the <c>ID_LYDONL</c> column value.
		/// This column is nullable.
		/// </summary>
		/// <value>The <c>ID_LYDONL</c> column value.</value>
		public int ID_LYDONL
		{
			get
			{
				return _id_lydonl;
			}
			set
			{
				_id_lydonl = value;
			}
        }

        /// <summary>
        /// Gets or sets the <c>GHICHU</c> column value.
        /// This column is nullable.
        /// </summary>
        /// <value>The <c>GHICHU</c> column value.</value>
        public string GHICHU
        {
            get
            {
                return _ghichu;
            }
            set
            {
                _ghichu = value;
            }
        }
        #endregion

        public int ColumnLength(string column)
        {
            return (int)MITI.BaseDB.Instance.ExecSql_DataValue("select COL_LENGTH('DONVI','" + column + "')") / 2;
        }

        /// <summary>
        /// Kiem tra ton theo id_nbl va id_nhanvien
        /// </summary>
        /// <returns></returns>
        public bool isExists()
        {
            //Chuyen doi lai tu ngay kieu Viet Nam sang ngay kieu en-US
            CultureInfo cul = GeneralCode.GetCultureInfoUS();
            DateTimeFormatInfo nfi = cul.DateTimeFormat;

            //string strSQL = "select count(*) from view_LuongHienTai where id_nbl = " + _id_nbl + " and id_nhanvien = " + _id_nhanvien + "";
            string strSQL = "select count(*) from view_LuongHienTai where id_nhanvien = " + _id_nhanvien + " and namxepluong = '" + _namxepluong.ToString("d", nfi) + "'";
            int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
            if (value > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// Lấy thuộc tính của lớp đối tượng theo string name.
        /// Dùng hàm này để các hàm insert, update có thể dùng câu lệnh for cho các thuộc tính
        /// </summary>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>Giá trị của thuộc tính đó.</returns>
        /// 
        public object GetValueByName(string paramName)
        {
            object obj = null;
            switch (paramName)
            {
                case "ID_LUONG":
                    obj = _id_luong;
                    break;
                case "ID_NHANVIEN":
                    obj = _id_nhanvien;
                    break;
                case "ID_NBL":
                    obj = _id_nbl;
                    break;
                case "NAMXEPLUONG":
                    obj = _namxepluong;
                    break;
                case "PHUCAPTNVK":
                    obj = _phucaptnvk;
                    break;
                case "HESOCLBL":
                    obj = _hesoclbl;
                    break;
                case "HESOCLBL2":
                    obj = _hesoclbl2;
                    break;
                case "ID_LYDONL":
                    obj = _id_lydonl;
                    break;
                case "GHICHU":
                    obj = _ghichu;
                    break;
            }
            return obj;
        }

        /// <summary>
        /// Gán giá trị cho thuộc tính của lớp đối tượng theo string name.
        /// Dùng hàm này để các hàm insert, update có thể dùng câu lệnh for cho các thuộc tính
        /// </summary>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>Giá trị của thuộc tính đó.</returns>
        public void SetValueByName(string paramName, object value)
        {
            switch (paramName)
            {
                case "ID_LUONG":
                    _id_luong = (int)value;
                    break;
                case "ID_NHANVIEN":
                    _id_nhanvien = (int)value;
                    break;
                case "ID_NBL":
                    _id_nbl = (int)value;
                    break;
                case "NAMXEPLUONG":
                    _namxepluong = (value == DBNull.Value ? MyDate.DateNull() : (DateTime)value);
                    break;
                case "PHUCAPTNVK":
                    _phucaptnvk = (value == DBNull.Value ? 0 : (int)value);
                    break;
                case "HESOCLBL":
                    _hesoclbl = (value == DBNull.Value ? 0 : float.Parse(value.ToString()));
                    break;
                case "HESOCLBL2":
                    _hesoclbl2 = (value == DBNull.Value ? 0 : float.Parse(value.ToString()));
                    break;
                case "ID_LYDONL":
                    _id_lydonl = (int)value;
                    break;
                case "GHICHU":
                    _ghichu = value.ToString();
                    break;
            }
        }
    }

    public class CLUONGCollection
    {
        public static string[] _params = { 
                "ID_LUONG"
               ,"ID_NHANVIEN"
               ,"ID_NBL"
               ,"NAMXEPLUONG"
               ,"PHUCAPTNVK"
               ,"HESOCLBL"
               ,"HESOCLBL2"
               ,"ID_LYDONL"
               ,"GHICHU"};


        /// <summary>
        /// Bang luong hien tai cua nhan vien
        /// </summary>
        /// <param name="id_nhanvien"></param>
        /// <returns></returns>
        public static CLUONG LayLuongHienTaiTheoID_NHANVIEN(int id_nhanvien)
        {
            CLUONG obj;
            string strSQL = "select * from LUONG where ";
            strSQL += "id_nhanvien = " + id_nhanvien.ToString();
            strSQL += " and namxepluong = (select max(namxepluong) from LUONG where id_nhanvien = " + id_nhanvien.ToString() + ")";
            obj = GetLUONGTheoSQL(strSQL);
            return obj;
        }

        /// <summary>
        /// Lay qua trinh tang luong cua nhan vien
        /// </summary>
        /// <param name="id_nhanvien"></param>
        /// <returns></returns>
        public static DataSet LayQuaTrinhTangLuongTheoID_NHANVIEN(int id_nhanvien)
        {
            DataSet ds;
            string strSQL = "select * from LUONG where ";
            strSQL += "id_nhanvien = " + id_nhanvien.ToString();
            strSQL += " order by namxepluong asc";
            ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            return ds;
        }

        /// <summary>
        /// Tra ve doi tuong CLUONG voi cau lenh SQL bat ky dang "Select * from LUONG ..."
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static CLUONG GetLUONGTheoSQL(string strSQL)
        {
            SqlDataReader objReader = null;
            CLUONG obj = null;
            try
            {
                objReader = MITI.BaseDB.Instance.ExecSql_DataReader(strSQL);
                while (objReader.Read())
                {
                    obj = GetObjectByDataReader(objReader);
                }
                if ((objReader != null))
                {
                    objReader.Close();
                }
                MITI.BaseDB.Instance.CloseConnect();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return obj;
        }

        /// <summary>
        /// Ham nay dung de tao ra doi tuong CLUONG voi dau vao la SqlDataReader
        /// </summary>
        /// <param name="objReader"> Dau vao la doi tuong SqlDataReader</param>
        /// <returns></returns>
        public static CLUONG GetObjectByDataReader(SqlDataReader objReader)
        {
            CLUONG obj = new CLUONG();
            //Bắt đầu từ i = 1 để không lấy ID
            for (int i = 0; i < _params.Length; i++)
            {
                obj.SetValueByName(_params[i], objReader.GetValue(i));
            }
            return obj;
        }

        public static bool Insert(CLUONG objLUONG)
        {
            StringBuilder objStringBuilder = new StringBuilder(250);

            objStringBuilder.Append("INSERT INTO LUONG VALUES(");
            //Bắt đầu từ i = 1 để không lấy ID
            for (int i = 1; i < _params.Length; i++)
            {
                if (_params[i] == "GHICHU")
                    objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], false));
                else
                    objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], true));
            }
            objStringBuilder.Append(")");
            
            string sInsert = objStringBuilder.ToString();

            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(sInsert, CommandType.StoredProcedure);
            long lRowsAffected = 0;
            try
            {
                {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    //Bắt đầu từ i = 1 để không lấy ID
                    for (int i = 1; i < _params.Length; i++)
                    {
                        AddParameter(ref cmd, _params[i], objLUONG.GetValueByName(_params[i]));
                    }
                    lRowsAffected = cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return true;
        }

        public static long Update(CLUONG objLUONG)
        {
            long lRowsAffected = 0;
            string strSQL = "UPDATE LUONG SET";
            //Bắt đầu từ i = 1 để không lấy ID
            for (int i = 1; i < _params.Length; i++)
            {
                if (_params[i] == "GHICHU")
                    strSQL += " " + _params[i] + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], false);
                else
                    strSQL += " " + _params[i] + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], true);
            }
            //Mệnh đề where lấy ID
            strSQL += " WHERE " + _params[0] + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(_params[0], false);

            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text);
            try
            {
                cmd.Transaction = cmd.Connection.BeginTransaction();
                //Bắt đầu từ i = 1 để không lấy ID
                for (int i = 1; i < _params.Length; i++)
                {
                    AddParameter(ref cmd, _params[i], objLUONG.GetValueByName(_params[i]));
                }
                //Mệnh đề where lấy ID
                AddParameter(ref cmd, _params[0], objLUONG.GetValueByName(_params[0]));
                lRowsAffected = cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return lRowsAffected;
        }

        protected static SqlParameter AddParameter(ref SqlCommand cmd, string paramName, object value)
        {
            SqlParameter parameter = null;
            switch (paramName)
            {
                //Kieu Int
                case "ID_LUONG":
                case "ID_NHANVIEN":
                case "ID_NBL":
                case "ID_LYDONL":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Int32, ((int)value == -1 ? DBNull.Value : value));
                    break;
                case "PHUCAPTNVK":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Int32, (int)value);
                    break;
                //Kieu double:
                case "HESOCLBL":
                case "HESOCLBL2":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Single, (float)value);
                    break;
                //Kieu Date
                case "NAMXEPLUONG":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Date, ((DateTime)value == MyDate.DateNull() ? DBNull.Value : value));
                    break;
                //Kieu String
                case "GHICHU":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.String, (value == null ? DBNull.Value : value));
                    break;
            }
            return parameter;
        }
    }
}
